--========================--
--section: create database--
--========================--

use master;
go

drop database online_news_editorial;
go

create database online_news_editorial;
go

use online_news_editorial;
go


--======================--
--section: create tables--
--======================--

create table Divisions
(
	divId char(6) not null,--div999
	divName varchar(50) not null,
	divDescription varchar(150),
	--divManager uniqueidentifier not null,
	
	constraint pk_Divisions primary key(divId),
	constraint unq_Divisions unique (divName),
	constraint chk_Divisions check
	(
		divId like 'div[0-9][0-9][0-9]'
	),
);
go



create table Jobs
(
	jobId char(7) not null,--job9999
	jobTitle varchar(50) not null,
	jobDescription varchar(150),
	maxSalary money,
	minSalary money,
	
	constraint pk_Jobs primary key(jobId),
	constraint unq_Jobs unique (jobTitle),
	constraint chk_Jobs check
	(
		jobId like 'job[0-9][0-9][0-9][0-9]'
		and	maxSalary >= 1
		and minSalary >= 1
		and maxSalary > minSalary
	)
);
go



create table Employees
(
	empId char(9) not null,--emp999999
	userName varchar(256) not null,
	userId uniqueidentifier not null,
	empFirstName varchar(50) not null,
	empLastName varchar(50) not null,
	empGender bit not null default 'true',
	empJoinDate datetime not null,
	empLeaveDate datetime,
	empSalary money,
	
	constraint pk_Employees primary key(empId),
	constraint fk_Employees_Users foreign key(userId) references aspnet_Users(userId),
	constraint chk_Employees check
	(
		empId like 'emp[0-9][0-9][0-9][0-9][0-9][0-9]'
		--and empSalary <= (select maxSalary from Jobs)
		--and empSalary >= (select minSalary from Jobs)
	),
);
go



create table JobAssignments
(
	empId char(9) not null,
	divId char(6) not null,
	jobId char(7) not null,
	dateStart datetime not null,
	dateFinish datetime,
	assignmentDescription varchar(150),
	
	constraint pk_JobAssignments primary key(empId, dateStart, divId),
	constraint fk_JobAssignments_Employees foreign key(empId) references Employees(empId),
	constraint fk_JobAssignments_Divisions foreign key(divId) references Divisions(divId),
	constraint fk_JobAssignments_Jobs foreign key(jobId) references Jobs(jobId),
);
go



create table Categories
(
	catId char(7) not null,--cat9999
	catParentId char(7),
	catName varchar(50) not null,
	catDescription varchar(150),
	belongToDivision char(6) not null,
	
	constraint pk_Categories primary key(catId),
	constraint fk_Categories foreign key(catParentId) references Categories(catId),
	constraint fk_Categories_Divisions foreign key(belongToDivision) references Divisions(divId),
	constraint unq_Categories unique (catName),
);
go



create table News
(
	newsId uniqueidentifier not null default newid(),--pk
	catId char(7) not null,--fk
	newsTitle varchar(150) not null,
	newsIntroduceText varchar(1000) not null,
	newsContent varchar(max) not null,
		author char(9) not null,--fk, display in web as a author.
		postingDate datetime not null,
		modificationDate datetime,
			processLevel int not null default -1,
				verified bit default 'false',
				publisher char(9),--fk
				publishDate datetime,--display in web as a posting date.
				unpublishDate datetime,
	numberOfView int default 0,
	
	constraint pk_News primary key(newsId),
	constraint fk_News_Categories foreign key(catId) references Categories(catId),
	constraint fk_News_Employees_As_Author foreign key(author) references Employees(empId),
	constraint fk_News_Employees_As_Publisher foreign key(publisher) references Employees(empId),
	constraint unq_News unique(newsTitle),
	constraint chk_News check
	(
		numberOfView >= 0
		and processLevel = -1--(-1): writer.
		or processLevel = 0--(0): editor.
		or processLevel = 1--(1): approver.
	),
);
go



create table NewsVersions
(
	newsId uniqueidentifier not null,
	verId int not null,
	verEditor char(9) not null,
	verSender char(9) not null,
	verProcessDirection int not null default -1,
	verComments varchar(1000),
	verDate datetime not null,
	
	constraint pk_NewsVersions primary key(newsId, verId),
	constraint fk_NewsVersions_News	foreign key(newsId) references News(newsId),
	constraint fk_NewsVersions_Employees_As_Editor	foreign key(verEditor) references Employees(empId),
	constraint fk_NewsVersions_Employees_As_Sender	foreign key(verSender) references Employees(empId),
	constraint chk_NewsVersions check
	(
		verProcessDirection = -1--cap tren tra~ bai` xuong.
		or verProcessDirection = 1--cap duoi gui bai` len.
	),
);
go



create table NewsRelated
(
	newsId uniqueidentifier,--pk, fk
	newsRelatedId uniqueidentifier,--pk, fk
	
	constraint pk_NewsRelated primary key(newsId, newsRelatedId),
	constraint fk_NewsRelated_News_1 foreign key(newsId) references News(newsId),
	constraint fk_NewsRelated_News_2 foreign key(newsRelatedId) references News(newsId),
);
go



create table Media
(
	mId uniqueidentifier not null,--pk
	mName varchar(100) not null,
	mDescription varchar(500),
	mData varbinary(max),
	mLocation varchar(1000),
	mType varchar(5) not null,
	mSize bigint,
	mCreateDate datetime not null,
	mUploadedBy char(9),--fk
	
	constraint pk_Media primary key(mId),
	constraint fk_Media_Employees foreign key(mUploadedBy) references Employees(empId),
	constraint chk_Media check
	(
		mType like 'video'
		or mType like 'audio'
		or mType like 'photo'
	)
);
go



create table MediaInNews
(
	newsId uniqueidentifier,--pk, fk
	mId uniqueidentifier,--pk, fk
	
	constraint pk_MediaInNews primary key(newsId, mId),
	constraint fk_MediaInNews_News foreign key(newsId) references News(newsId),
	constraint fk_MediaInNews_Media foreign key(mId) references Media(mId)
);
go



create table Comments
(
	comId uniqueidentifier not null default newid(),--pk
	newsId uniqueidentifier not null,--fk
	writerName varchar(50)not null,
	commContent varchar(max) not null,
	postingDate datetime not null,
	isIllegal bit not null default 0,
	
	constraint pk_Comments primary key(comId),
	constraint fk_Comments_News foreign key(newsId) references News(newsId),
);
go



create table AdvertisementContacts
(
	advContId uniqueidentifier not null default newid(),--pk
	advContName varchar(50) not null,
	advContCompany varchar(50),
	advContPhone varchar(11),
	
	constraint pk_AdvertisementContact primary key(advContId),
);
go



create table Advertisements
(
	advId uniqueidentifier not null default newid(),--pk
	advContId uniqueidentifier not null,--fk
	advDescription varchar(150),
	advExpireDate datetime not null,
	advSizeWidth int not null,
	advSizeHeight int not null,
	advImage image,--should be image or varchar ?
	advLink varchar(1000),
	
	constraint pk_Advertisements primary key(advId),
	constraint fk_Advertisements_AdvertisementContacts foreign key(advContId) references AdvertisementContacts(advContId),
);
go




